
package vn.aptech.project.model.dao;
import java.io.FileNotFoundException;
import java.sql.SQLException;
import vn.aptech.project.model.dto.*;
import vn.aptech.project.control.*;
import vn.aptech.project.view.variable;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;

public class  tblorder_DAO extends CommonDAO {
 public  tblorder_DAO() throws ClassNotFoundException, SQLException , IOException{
 }


public Vector selectall() throws SQLException
{
String strSelect = "SELECT FS_order.orderid,FS_customer.cust_name,FS_customer.contactno,FS_customer.addr,FS_order.Order_date,FS_order.TotalAmount,FS_customer.Typeid,FS_Employee_Detail.staffname FROM FS_customer inner join FS_order on FS_customer.Customerid=FS_order.Customerid inner join FS_Employee_Detail on FS_order.Employeeid=FS_Employee_Detail.staffid";
return ExecSelect(strSelect);
}
public Vector totalorder() throws SQLException
{
String strSelect = "select tonghoadon=count('orderid') from FS_order";
return ExecSelect(strSelect);
}
public Vector toporder() throws SQLException
{
String strSelect = "select Top 1 TotalAmount from FS_order order by TotalAmount DESC";
return ExecSelect(strSelect);
}
public Vector topcustomer() throws SQLException
{
String strSelect = "select Top 1 FS_customer.cust_name from FS_order inner join FS_customer on FS_order.Customerid=FS_customer.Customerid group by FS_customer.cust_name order by sum(TOtalAmount) DESC";
return ExecSelect(strSelect);
}
public Vector SearchByCustomerName(String cust_name) throws SQLException
{
String strSelect = "SELECT FS_order.orderid,FS_customer.cust_name,FS_customer.contactno,FS_customer.addr,FS_order.Order_date,FS_order.TotalAmount,FS_customer.Typeid,FS_Employee_Detail.staffname FROM FS_customer inner join FS_order on FS_customer.Customerid=FS_order.Customerid inner join FS_Employee_Detail on FS_order.Employeeid=FS_Employee_Detail.staffid where FS_customer.cust_name like '%"+cust_name+"%'";
return ExecSelect(strSelect);
}
public Vector SearchByEmpName(String staffname) throws SQLException
{
String strSelect = "SELECT FS_order.orderid,FS_customer.cust_name,FS_customer.contactno,FS_customer.addr,FS_order.Order_date,FS_order.TotalAmount,FS_customer.Typeid,FS_Employee_Detail.staffname FROM FS_customer inner join FS_order on FS_customer.Customerid=FS_order.Customerid inner join FS_Employee_Detail on FS_order.Employeeid=FS_Employee_Detail.staffid where FS_Employee_Detail.staffname like '%"+staffname+"%'";
return ExecSelect(strSelect);
}
public Vector SearchByProductName(String productname) throws SQLException
{
String strSelect = "SELECT FS_order.orderid,FS_customer.cust_name,FS_customer.contactno,FS_customer.addr,FS_order.Order_date,FS_order.TotalAmount,FS_customer.Typeid,FS_Employee_Detail.staffname FROM FS_customer inner join FS_order on FS_customer.Customerid=FS_order.Customerid inner join FS_orderline on FS_order.orderid=FS_orderline.orderid inner join FS_product on FS_orderline.productid=FS_product.productid inner join FS_category on FS_product.Typeid=FS_category.Typeid inner join FS_Employee_Detail on FS_category.typeinfo=FS_Employee_Detail.department where productname like '%"+productname+"%'";
return ExecSelect(strSelect);
}
public int getCustomerIdByOrderid(int orderid) throws SQLException, FileNotFoundException, IOException
{
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
             } catch (ClassNotFoundException ex)
            {
            Logger.getLogger(tblorder_DAO.class.getName()).log(Level.SEVERE, null, ex);
            }
            String connectionString = "jdbc:sqlserver://localhost:1433;databaseName=FurnitureSystem;user=sa;password=1234567";
            Connection con = DriverManager.getConnection(connectionString);
            Statement statement = con.createStatement();
            String str = "Select Customerid from FS_order where orderid="+orderid;
            ResultSet rs = statement.executeQuery(str);
            rs.next();
            Vector vt = new Vector();
            vt.add(rs.getObject("Customerid"));
            int a =Integer.parseInt(String.valueOf(vt.get(0)));
            return a;
       
}
public int getOrderidByCusid(int CustomerId) throws SQLException, FileNotFoundException, IOException
{
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
             } catch (ClassNotFoundException ex)
            {
            Logger.getLogger(tblorder_DAO.class.getName()).log(Level.SEVERE, null, ex);
            }
            String connectionString = "jdbc:sqlserver://localhost:1433;databaseName=FurnitureSystem;user=sa;password=1234567";
            Connection con = DriverManager.getConnection(connectionString);
            Statement statement = con.createStatement();
            String str = "Select orderid from FS_order where Customerid="+CustomerId;
            ResultSet rs = statement.executeQuery(str);
            rs.next();
            Vector vt = new Vector();
            vt.add(rs.getObject("orderid"));
            int a =Integer.parseInt(String.valueOf(vt.get(0)));
            return a;

}
public Vector SearchByOrderId(int orderid) throws SQLException
{
String strSelect = "SELECT FS_order.orderid,FS_customer.cust_name,FS_customer.contactno,FS_customer.addr,FS_order.Order_date,FS_order.TotalAmount,FS_customer.Typeid,FS_Employee_Detail.staffname FROM FS_customer inner join FS_order on FS_customer.Customerid=FS_order.Customerid inner join FS_Employee_Detail on FS_order.Employeeid=FS_Employee_Detail.staffid where FS_order.orderid="+orderid;
return ExecSelect(strSelect);
}
public Vector SearchByOrderIdAndCustName(int orderid,String cust_name) throws SQLException
{
String strSelect = "SELECT FS_order.orderid,FS_customer.cust_name,FS_customer.contactno,FS_customer.addr,FS_order.Order_date,FS_order.TotalAmount,FS_customer.Typeid,FS_Employee_Detail.staffname FROM FS_customer inner join FS_order on FS_customer.Customerid=FS_order.Customerid iinner join FS_Employee_Detail on FS_order.Employeeid=FS_Employee_Detail.staffid where FS_order.orderid="+orderid+" and FS_ustomer.cust_name like '%"+cust_name+"%'";
return ExecSelect(strSelect);
}
public int insert(tblorder_DTO  obj) throws SQLException
{
String strInsert = "INSERT INTO FS_order VALUES ("+obj.getCustomerid() +" ,getdate(),"+obj.getStaffid()+","+obj.getTotalAmount()+")";
return  ExecNonQuery(strInsert);
}


public int update(tblorder_DTO  obj) throws SQLException
{
String strUpdate = "UPDATE FS_order SET Customerid="+obj.getCustomerid() +","+"Order_date=getdate(),TotalAmount="+obj.getTotalAmount()+" WHERE orderid="+ obj.getorderid() ;
return  ExecNonQuery(strUpdate);
}


public int delete(int orderid) throws SQLException
{
String strDelete = "DELETE FROM FS_order WHERE orderid="+orderid;
return  ExecNonQuery(strDelete);
}

public Vector selectall1() throws SQLException, ClassNotFoundException, IOException
{
    Employee_Detail_BLL edb = new Employee_Detail_BLL();
    variable.Staffid = edb.getStaffidByStaffName(variable.StaffName);
String strSelect = "SELECT FS_order.orderid,FS_customer.cust_name,FS_customer.contactno,FS_customer.addr,FS_order.Order_date,FS_order.TotalAmount,FS_customer.Typeid,FS_Employee_Detail.staffname FROM FS_customer inner join FS_order on FS_customer.Customerid=FS_order.Customerid inner join FS_Employee_Detail on FS_order.Employeeid=FS_Employee_Detail.staffid where FS_Employee_Detail.staffid='"+variable.Staffid+"'";
return ExecSelect(strSelect);
}
public Vector SearchByCustomerName1(String cust_name) throws SQLException, ClassNotFoundException, FileNotFoundException, IOException
{
        Employee_Detail_BLL edb = new Employee_Detail_BLL();
    variable.Staffid = edb.getStaffidByStaffName(variable.StaffName);
String strSelect = "SELECT FS_order.orderid,FS_customer.cust_name,FS_customer.contactno,FS_customer.addr,FS_order.Order_date,FS_order.TotalAmount,FS_customer.Typeid,FS_Employee_Detail.staffname FROM FS_customer inner join FS_order on FS_customer.Customerid=FS_order.Customerid inner join FS_Employee_Detail on FS_order.Employeeid=FS_Employee_Detail.staffid where FS_customer.cust_name like '%"+cust_name+"%' and FS_Employee_Detail.staffid='"+variable.Staffid+"'";
return ExecSelect(strSelect);
}
public Vector SearchByProductName1(String productname) throws SQLException, ClassNotFoundException, IOException
{
        Employee_Detail_BLL edb = new Employee_Detail_BLL();
    variable.Staffid = edb.getStaffidByStaffName(variable.StaffName);
String strSelect = "SELECT FS_order.orderid,FS_customer.cust_name,FS_customer.contactno,FS_customer.addr,FS_order.Order_date,FS_order.TotalAmount,FS_customer.Typeid,FS_Employee_Detail.staffname FROM FS_customer inner join FS_order on FS_customer.Customerid=FS_order.Customerid inner join FS_orderline on FS_order.orderid=FS_orderline.orderid inner join FS_product on FS_orderline.productid=FS_product.productid inner join FS_category on FS_product.Typeid=FS_category.Typeid inner join FS_Employee_Detail on FS_category.typeinfo=FS_Employee_Detail.department where productname='"+productname+"' and FS_Employee_Detail.staffid='"+variable.Staffid+"'";
return ExecSelect(strSelect);
}
public Vector SearchByOrderId1(int orderid) throws SQLException, ClassNotFoundException, FileNotFoundException, IOException
{
        Employee_Detail_BLL edb = new Employee_Detail_BLL();
    variable.Staffid = edb.getStaffidByStaffName(variable.StaffName);
String strSelect = "SELECT FS_order.orderid,FS_customer.cust_name,FS_customer.contactno,FS_customer.addr,FS_order.Order_date,FS_order.TotalAmount,FS_customer.Typeid,FS_Employee_Detail.staffname FROM FS_customer inner join FS_order on FS_customer.Customerid=FS_order.Customerid inner join FS_Employee_Detail on FS_order.Employeeid=FS_Employee_Detail.staffid where FS_order.orderid like "+orderid+" and FS_Employee_Detail.staffid='"+variable.Staffid+"'";
return ExecSelect(strSelect);
}
public Vector SearchByOrderIdAndCustName1(int orderid,String cust_name) throws SQLException, ClassNotFoundException, IOException
{
        Employee_Detail_BLL edb = new Employee_Detail_BLL();
    variable.Staffid = edb.getStaffidByStaffName(variable.StaffName);
String strSelect = "SELECT FS_order.orderid,FS_customer.cust_name,FS_customer.contactno,FS_customer.addr,FS_order.Order_date,FS_order.TotalAmount,FS_customer.Typeid,FS_Employee_Detail.staffname FROM FS_customer inner join FS_order on FS_customer.Customerid=FS_order.Customerid inner join FS_Employee_Detail on FS_order.Employeeid=FS_Employee_Detail.staffid where FS_order.orderid like "+orderid+" and FS_customer.cust_name like '%"+cust_name+"%' and FS_Employee_Detail.staffname='"+variable.StaffName+"'";
return ExecSelect(strSelect);
}
}